
* Get underlying loan data 
use ../Intermediate/transaction_clean, clear
joinby transaction_id using ../Intermediate/transaction_lender_clean //merge in the lenders for each instrument

drop if missing(BankID)
drop if amount_trans_usdm <= 0 | missing(amount_trans_usdm) | amount_trans_lender_usdm < 0
drop if missing(date_trans)
drop if obs_ammendment == 1
gen year = yofd(date_trans)

//Adding non-bank transactions Data 
append using "../Raw/psedo/aggregate_non_bank_trans"

//Merging in id for non-bank
merge m:1 BankID using "../Intermediate/non_bank_list", keep(1 3) nogen
gen non_bank = is_bank == 0 


joinby BankID year using ../Intermediate/bank_ban_strength_yearly, unmatched(master) 
gen no_ban_data = _merge == 1
drop _merge
replace active_policy = 0 if no_ban_data == 1
merge m:1 borrower_id BankID using ../Intermediate/exposure_shares, nogen 

//period of interest
gen period_pre  = inrange(date_trans,d(01jan2009),d(01jan2014)) 

//debt type: loan, bond all
gen dtype_bond = bond_ind == 1
gen dtype_loan = bond_ind == 0
gen dtype_debt = 1

//policy type
gen borr_debt                    = amount_trans_lender_usdm * dtype_debt
gen borr_debt_actvpol            = amount_trans_lender_usdm * dtype_debt * active_policy
gen borr_debt_has_coded_policy   = amount_trans_lender_usdm * dtype_debt * has_coded_policy

// relationship‐ & period‐specific borrowing
gen borr_debt_rel_all_pre               = borr_debt * (exp_share_pre_all_debt > 0)
gen borr_debt_rel_all_pre_actvpol       = borr_debt * (exp_share_pre_all_debt > 0) * active_policy
gen borr_debt_rel_all_pre_haspol        = borr_debt * (exp_share_pre_all_debt > 0) * has_coded_policy


//Any lender is a non-bank
gen borr_loan = amount_trans_lender_usdm*dtype_loan
gen borr_debt_non_bank = amount_trans_lender_usdm*non_bank
gen borr_debt_non_bank_og_sample = borr_debt_non_bank
replace borr_debt_non_bank = borr_debt_non_bank + borr_debt if non_bank_deals == 1 //Adding non-bank deals
gen borr_debt_gov = amount_trans_lender_usdm*gov_actor
gen borr_debt_corp = amount_trans_lender_usdm*non_financial
gen borr_debt_private_non_bank = amount_trans_lender_usdm*asset_manager_or_pe
gen borr_debt_non_bank_other = amount_trans_lender_usdm*non_bank_other


//collapse lending to borrower year level 
preserve
collapse (sum) borr_* , by(borrower_id year)

//get shares
foreach dtype in debt  {
	gen shr_`dtype'_actvpol = borr_`dtype'_actvpol/borr_`dtype'
foreach reltype in all {
foreach period in pre {
	gen shr_`dtype'_rel_`reltype'_`period'_actvpol = borr_`dtype'_rel_`reltype'_`period'_actvpol/borr_`dtype'
	gen shr_`dtype'_rel_`reltype'_`period'_haspol = borr_`dtype'_rel_`reltype'_`period'_haspol/borr_`dtype'
	gen shr_`dtype'_rel_`reltype'_`period' = borr_`dtype'_rel_`reltype'_`period'/borr_`dtype'
}
}
	gen shr_`dtype' = borr_`dtype'/borr_debt
}
drop shr_debt

tempfile borr 
save `borr'
restore


tabulate debt_type, gen(dt)
gen dt_allbonds = bond_ind == 1
rename dt2 dt_bridge
rename dt3 dt_cp 
rename dt4 dt_loc
rename dt5 dt_mtn
rename dt9 dt_revolver
rename dt11 dt_term


gcollapse (mean) project_finance refinance_ind dt_* spread_bps coupon_bps [aweight=amount_trans_lender_usdm], by(borrower_id year) missing

merge 1:1 borrower_id year using `borr', keep(3) nogen


ds borrower_id year, not
local fillvars `r(varlist)'

tsset borrower_id year
gen orig = 1
tsfill, full 
foreach var of var `fillvars' {
	replace `var' = 0 if orig == .
}
replace spread_bps = . if orig == .
replace coupon_bps = . if orig == .
replace orig = 0 if missing(orig)
label var orig "has loan or bond in this year"
compress
save ../Intermediate/borrowing_panel, replace


